#!/usr/bin/python
# coding=utf-8
import traceback
import psycopg2
import time
import sys
import sqlparse
import parse_query

#处理UnicodeDecodeError: 'ascii' codec can't decode byte 0xe9 in position 0: ordinal not in range(128)
default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    reload(sys)
    sys.setdefaultencoding(default_encoding)

#连接即将执行query的数据库
def conn_db(order_id):
    # print '[{0}] 连接数据库 ...'.format(time.strftime('%X'))
    #连接部署数据库，获取目标执行数据库的连接方式
    conn = psycopg2.connect("host=opsdb.yazuoyw.com dbname=ops port=5432 user=opser password=opsEr3469")
    cur = conn.cursor()
    sql = "select 'host='||address||' '||'dbname='||dbname||' '||'port='||port||' '||'user='||databases.user||' '||'password='||password from sql_orders,databases where sql_orders.database_id=databases.id and sql_orders.id=%s;" %order_id
    cur.execute(sql)
    db_access, = cur.fetchall()

    #定义全局变量
    global desc_db
    desc_db = db_access[0]
    return (conn)

# 以元组的形式获取上传的query语句，并过滤掉换行符
def get_query(order_id):
    f.write('[{0}] 执行query_sql ...\n'.format(time.strftime('%X')))
    query_sql = 'select script from sql_contents where sql_order_id=%s;'
    cur_control.execute(query_sql, (order_id,))
    f.write('[{0}] commit query_sql ...\n'.format(time.strftime('%X')))
    conn_control.commit()
    query_lists, = cur_control.fetchall()
    f.write('[{0}] 获取query_list ...\n'.format(time.strftime('%X')))
    query_list = sqlparse.split(query_lists[0].strip('\n').strip('\r\n').strip('\r'))
    f.write('[{0}] get_query方法完成 ...\n'.format(time.strftime('%X')))
    return query_list

#检测要执行的订单号状态，如果未执行则执行running_query(),如果是异常状态则继续执行（repeat_query()）
def audit_query(order_id):
    #连接数据库
    global f
    f = open('./python_report.log','a')
    f.write('[{0}] 开始执行上线单--%s ...\n'.format(time.strftime('%X')) %order_id)
    f.write('[{0}] 连接数据库 ...\n'.format(time.strftime('%X')))
    global conn_control
    conn_control = conn_db(order_id)
    global cur_control
    cur_control = conn_control.cursor()
    global conn_execute
    conn_execute = psycopg2.connect(desc_db)
    global cur_execute
    cur_execute = conn_execute.cursor()

    #更改订单的执行状态
    execut_status = "update sql_orders set status= 'executing',exec_time=now() where id =%s;"
    cur_control.execute(execut_status, (order_id,))
    conn_control.commit()

    #获取上线单的状态
    query_status = 'select done_entries,status from sql_orders where id = %s;'
    cur_control.execute(query_status, (order_id,))
    query_status_list, = cur_control.fetchall()
    query_status = query_status_list[1]

    #判断上线单的状态是不是executing，如果是就继续执行，否则退出
    if query_status == 'executing':
        error_num = running_query(order_id)
        execute_status(error_num,order_id)
    else:
        print('Can not continue to execution the program')

    #关闭数据库连接
    f.write('[{0}] 上线单执行完成,关闭数据库连接 ...\n'.format(time.strftime('%X')))
    conn_control.close()
    conn_execute.close()
    f.closed

#执行异常的订单，跳过已经执行的query
def running_query(order_id):

    #获取已经执行query的个数
    query_status = 'select done_entries,status from sql_orders where id = %s;'
    cur_control.execute(query_status, (order_id,))
    query_status_list, = cur_control.fetchall()
    current_query = query_status_list[0]

    last_query_num = 1
    success_num = 0
    error_num = 0
    done_num = 0
    last_query_list = ''
    error_info = ''

    #调用parse_query模块，获得即将执行的query列表，已经执行过的query将跳过
    f.write('[{0}] 读取上线单SQL ...\n'.format(time.strftime('%X')))
    query_list = get_query(order_id)
    f.write('[{0}] 执行上线单SQL ...\n'.format(time.strftime('%X')))
    for query in query_list:
        #判断当前的query值是否大于库中已经记录的值，如果大于数据库中的值则执行此query，否则跳过
        if last_query_num > current_query:
            last_query_num += 1
            last_query = query
            last_query_list = last_query_list + str(last_query)

            #记录已经执行query的条数
            done_num = last_query_num - 1
            update_done_entries = "update sql_orders set done_entries=%s where id=%s"
            cur_control.execute(update_done_entries,(done_num,order_id))
            conn_control.commit()

            #在目标数据库中执行query，将错误query返回到报告中，并继续执行
            try:
                cur_execute.execute(unicode(query))
                rowcount = cur_execute.rowcount
                if rowcount < 2:
                    conn_execute.commit()
                    success_num += 1
                else:
                    conn_execute.rollback()
                    f.write('影响数据条数大于1条：%s' %query)
                    error_info = error_info + '\n' + '影响数据条数大于1条：\n%s' %query
                    error_num += 1
            except Exception as e:
                #记录详细错误记录，包括失败总数，失败的query及失败原因
                error_num += 1
                error_query = str(query) + '\n' + str(e)
                error_info = error_info + error_query

                #重建目标执行库的连接，并且继续执行
                conn_execute.reset()
        else:
            last_query_num += 1
            last_query = query
            last_query_list = last_query_list +'\n' + str(last_query)

    #将执行query的执行明细记录到明细表中
    f.write('[{0}] 记录执行明细 ...\n'.format(time.strftime('%X')))
    last_excute_query = "update sql_contents set detail=%s where sql_order_id=%s;"
    cur_control.execute(last_excute_query,(last_query_list,order_id))
    conn_control.commit()

    #将执行报告返回到数据库中
    f.write('[{0}] 返回执行报告 ...\n'.format(time.strftime('%X')))
    execute_report(done_num,success_num,error_num,error_info,order_id)

    return error_num

#将错误报告返回到数据库中
def execute_report(done_num,success_num,error_num,error_info,order_id):

    error_report = "执行总数:%s \n\n成功总数:%s \n\n错误总数:%s \n\n错误原因:\n%s" %(done_num,success_num,error_num,error_info)
    last_excute_query = "update sql_contents set report=%s where sql_order_id=%s;"
    error_num_query = "update sql_orders set error_entries=%s where id=%s"

    cur_control.execute(last_excute_query,(error_report,order_id))
    conn_control.commit()
    cur_control.execute(error_num_query,(error_num,order_id))
    conn_control.commit()

#返回执行状态报告
def execute_status(error_num,order_id):
    #判断是不是有执行失败的SQL，如果有则返回执行状态为incomplete
    if error_num == 0:
        finish_status = "update sql_orders set status= 'succeeded',fin_time=now() where id =%s;"
    else :
        finish_status = "update sql_orders set status= 'incomplete',fin_time=now() where id =%s;"

    cur_control.execute(finish_status, (order_id,))
    conn_control.commit()

# 抛出脚本执行异常信息
def except_fun(order_id, execpt):
    print('[{0}] 执行running_query脚本报错...'.format(time.strftime('%X')))
    conn = conn_db(order_id)
    cur = conn.cursor()
    report_text = "%s" %execpt
    report_sql = "update sql_contents set report=%s where sql_order_id=%s;"
    finish_status = "update sql_orders set status= 'failed',fin_time=now() where id =%s;"
    cur.execute(finish_status, (order_id,))
    conn.commit()
    cur.execute(report_sql, (report_text, order_id))
    conn.commit()
    conn.close()

if __name__ == "__main__":
    try:
        audit_query(sys.argv[1])
    except Exception,e:
        exstr = traceback.format_exc()
        except_fun(sys.argv[1],exstr)
